{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Virus Total Lookup\n",
    "VTLookupV3 is a module in the MSTICPy library that provides integration with the VirusTotal API for querying file and URL reputation data. The module includes a `VTLookupV3` class that can be used to submit queries to the VirusTotal API and retrieve response data.\n",
    "\n",
    "VTLookupV3 is a powerful tool for security researchers and analysts who need to quickly and easily query VirusTotal for reputation data on files and URLs, and it is just one of many useful modules in the MSTICPy library.\n",
    "This notebook describes the use of the `VTLookupV3` capabilities in MSTICPy. \n",
    "\n",
    "This notebook aims to demonstrate the capabilities of `VTLookupV3` using an example investigation inspired by [F5: Attackers Use New, Sophisticated Ways to Install Cryptominers](https://www.f5.com/labs/articles/threat-intelligence/attackers-use-new--sophisticated-ways-to-install-cryptominers). In order to maximize the value of `VTLookupV3` some queries, like search require [Virus Total Enterprise License](https://support.virustotal.com/hc/en-us/articles/360001387057-VirusTotal-Intelligence-Introduction).\n",
    "\n",
    "\n",
    "## Features\n",
    "To improve readability, the capabilities of VTLookupV3 are encapsulated into a single, easy to access class. Most functions within the VTLookupV3 class can be done for either a single IOC, or a DataFrame of IOCs. \n",
    "\n",
    "| Single IOC methods | Multiple IOCs methods | Description |\n",
    "|--------------------|-----------------------|-------------|\n",
    "| VTLookupV3.lookup_ioc | VTLookupV3.lookup_iocs | Queries VT API for detection and details of provided IOC(s) |\n",
    "| VTLookupV3.lookup_ioc_relationships | VTLookupV3.lookup_iocs_relationships | Queries VT API for a specific relationship type for provided IOC(s) |\n",
    "| VTLookupV3.get_object | VTLookupV3.search | Queries VT API for full information about IOC(s) |\n",
    "| VTLookupV3.get_file_behavior | N/A | Queries VT API for sandbox / detonation information about IOC(s) |"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initializing MSTICPy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Built-in Libraries\n",
    "import re\n",
    "from urllib3 import get_host\n",
    "\n",
    "# MSTICPy\n",
    "import msticpy as mp\n",
    "from msticpy.context.vtlookupv3 import VTLookupV3\n",
    "\n",
    "# Third-Party Libraries\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "# Initialize MSTICPy\n",
    "mp.init_notebook()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Configuration File\n",
    "MSTICPy contains robust configuration options to enable analysts to take a diverse approach to using selecting credentials, and pre-building queries. For more details see [MSTICPy Package Configuration](https://msticpy.readthedocs.io/en/latest/getting_started/msticpyconfig.html) documentation. Below outlines various `AuthKey` methods to obtain Virus Total API capabilities.\n",
    "\n",
    "\n",
    "```yaml\n",
    "TIProviders:\n",
    "  VirusTotal:\n",
    "    Args:\n",
    "      AuthKey:\n",
    "        KeyVault: MyKeyVault/MySecret\n",
    "      AuthKey:\n",
    "        EnvironmentVar: \"VIRUSTOTAL_AUTH\"\n",
    "      AuthKey: \"MY_API_KEY_IN_PLAINTEXT\"\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# uses settings from msticpyconfig.yaml\n",
    "vt_client = VTLookupV3()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hunting for Configuration Files related to Cryptomining\n",
    "Virus Total Intelligence Search is comprised of 40+ search modifiers to create extremely fine-tuned queries to narrow down investigations.\n",
    "\n",
    "| Modifier | Description |\n",
    "|----------|-------------|\n",
    "| engines  | Malware family identified by Anti-Virus |\n",
    "| tag      | Additional entity descriptor |\n",
    "| have     | Checks if the entity contains a specific attribute |\n",
    "\n",
    "\n",
    "```\n",
    "Query: engines:miner AND tag:json AND have:itw\n",
    "Description: Look for malware that is classified as a 'miner' that is tagged with 'json' and has an 'itw' characteristics\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>type</th>\n",
       "      <th>names</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd</td>\n",
       "      <td>file</td>\n",
       "      <td>config.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd</td>\n",
       "      <td>file</td>\n",
       "      <td>zbetcheckin_tracker_config.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>file</td>\n",
       "      <td>server2.0.0.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>file</td>\n",
       "      <td>C:\\Users\\&lt;USER&gt;\\Downloads\\server.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>file</td>\n",
       "      <td>C:\\Users\\user\\Desktop\\server.json</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                 id  type  \\\n",
       "0  d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd  file   \n",
       "0  d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd  file   \n",
       "0  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad  file   \n",
       "0  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad  file   \n",
       "0  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad  file   \n",
       "\n",
       "                                   names  \n",
       "0                            config.json  \n",
       "0        zbetcheckin_tracker_config.json  \n",
       "0                       server2.0.0.json  \n",
       "0  C:\\Users\\<USER>\\Downloads\\server.json  \n",
       "0      C:\\Users\\user\\Desktop\\server.json  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# perform lookup and limit results to 100 or fewer\n",
    "cryptominer_df = vt_client.search('engines:miner AND tag:json AND have:itw', 100)\n",
    "\n",
    "# filter down to necessary columns\n",
    "cryptominer_df = cryptominer_df[ ['id', 'type', 'names'] ]\n",
    "\n",
    "# expand out names list, and filter down to JSON file names\n",
    "cryptominer_df = cryptominer_df.explode('names')\n",
    "cryptominer_df = cryptominer_df[ cryptominer_df['names'].str.endswith('.json') ]\n",
    "\n",
    "display(cryptominer_df.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search for In-The-Wild (ITW) information\n",
    "\"In-The-Wild\" (ITW) is a term used in computer security to describe malware that is actively spreading and infecting systems in real-world situations, as opposed to being studied in a controlled laboratory environment. In the \n",
    "\n",
    "In the context of VirusTotal, [\"In-The-Wild\" (ITW)](https://support.virustotal.com/hc/en-us/articles/360001385897-File-search-modifiers) refers to the number of times a particular piece of malware has been detected in the wild by VirusTotal's network of antivirus engines. This metric can be used to assess the prevalence and severity of a particular malware threat. A high ITW score indicates that the malware is actively spreading and infecting systems, while a low score may suggest that the malware is relatively rare or has been contained."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source</th>\n",
       "      <th>source.name</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>target_type</th>\n",
       "      <th>relationship_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd</td>\n",
       "      <td>config.json</td>\n",
       "      <td>file</td>\n",
       "      <td>81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d</td>\n",
       "      <td>url</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd</td>\n",
       "      <td>zbetcheckin_tracker_config.json</td>\n",
       "      <td>file</td>\n",
       "      <td>81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d</td>\n",
       "      <td>url</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>server2.0.0.json</td>\n",
       "      <td>file</td>\n",
       "      <td>ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c</td>\n",
       "      <td>url</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>C:\\Users\\&lt;USER&gt;\\Downloads\\server.json</td>\n",
       "      <td>file</td>\n",
       "      <td>ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c</td>\n",
       "      <td>url</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>C:\\Users\\user\\Desktop\\server.json</td>\n",
       "      <td>file</td>\n",
       "      <td>ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c</td>\n",
       "      <td>url</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                             source  \\\n",
       "0  d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd   \n",
       "1  d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd   \n",
       "4  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad   \n",
       "5  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad   \n",
       "6  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad   \n",
       "\n",
       "                             source.name source_type  \\\n",
       "0                            config.json        file   \n",
       "1        zbetcheckin_tracker_config.json        file   \n",
       "4                       server2.0.0.json        file   \n",
       "5  C:\\Users\\<USER>\\Downloads\\server.json        file   \n",
       "6      C:\\Users\\user\\Desktop\\server.json        file   \n",
       "\n",
       "                                                             target  \\\n",
       "0  81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d   \n",
       "1  81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d   \n",
       "4  ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c   \n",
       "5  ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c   \n",
       "6  ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c   \n",
       "\n",
       "  target_type relationship_type  \n",
       "0         url          itw_urls  \n",
       "1         url          itw_urls  \n",
       "4         url          itw_urls  \n",
       "5         url          itw_urls  \n",
       "6         url          itw_urls  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# look up in-the-wild URLs associated with files in cryptominer_df\n",
    "cryptominer_itw_df = vt_client.lookup_iocs_relationships(cryptominer_df, 'itw_urls', 'id', 'type', all_props=True)\n",
    "\n",
    "# filter and reorder columns \n",
    "cryptominer_itw_df = cryptominer_itw_df[ ['source', 'source_type', 'target', 'target_type', 'relationship_type'] ]\n",
    "\n",
    "# join itw data on cryptominer_df\n",
    "cryptominer_itw_df = cryptominer_itw_df.merge(cryptominer_df, how='inner', left_on='source', right_on='id')\n",
    "\n",
    "# rename and reorder columns\n",
    "cryptominer_itw_df.rename(columns={'names': 'source.name'}, inplace=True)\n",
    "cryptominer_itw_df = cryptominer_itw_df[ ['source', 'source.name', 'source_type', 'target', 'target_type', 'relationship_type'] ]\n",
    "cryptominer_itw_df.drop_duplicates(inplace=True)\n",
    "\n",
    "display(cryptominer_itw_df.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enrich ITW Information\n",
    "By default `VTLookupV3` returns relationship data in the form of 'id' and 'type' to obtain human readable information, enrichment is needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>url</th>\n",
       "      <th>host</th>\n",
       "      <th>host.type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d</td>\n",
       "      <td>http://27.1.1.34:8080/docs/config.json</td>\n",
       "      <td>27.1.1.34</td>\n",
       "      <td>ip_address</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c</td>\n",
       "      <td>https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546</td>\n",
       "      <td>minerjson.oss-cn-beijing.aliyuncs.com</td>\n",
       "      <td>domain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4a97690d7813c24629ee4ac539503fb1f5a57ed98ded075dc8d785f570b50a6f</td>\n",
       "      <td>https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=637704108706432000</td>\n",
       "      <td>minerjson.oss-cn-beijing.aliyuncs.com</td>\n",
       "      <td>domain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>578e3967424a350fcebfc7389c814bba5f71d8c62f03649fd39101fe6de69029</td>\n",
       "      <td>https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638124776855263671</td>\n",
       "      <td>minerjson.oss-cn-beijing.aliyuncs.com</td>\n",
       "      <td>domain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b1868edae176ede468699c1cb094f611d4a1fef70f7f507736a03f14247f8666</td>\n",
       "      <td>https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=637703350378568000</td>\n",
       "      <td>minerjson.oss-cn-beijing.aliyuncs.com</td>\n",
       "      <td>domain</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                 id  \\\n",
       "0  81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d   \n",
       "0  ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c   \n",
       "0  4a97690d7813c24629ee4ac539503fb1f5a57ed98ded075dc8d785f570b50a6f   \n",
       "0  578e3967424a350fcebfc7389c814bba5f71d8c62f03649fd39101fe6de69029   \n",
       "0  b1868edae176ede468699c1cb094f611d4a1fef70f7f507736a03f14247f8666   \n",
       "\n",
       "                                                                                   url  \\\n",
       "0                                               http://27.1.1.34:8080/docs/config.json   \n",
       "0  https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546   \n",
       "0  https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=637704108706432000   \n",
       "0  https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638124776855263671   \n",
       "0  https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=637703350378568000   \n",
       "\n",
       "                                    host   host.type  \n",
       "0                              27.1.1.34  ip_address  \n",
       "0  minerjson.oss-cn-beijing.aliyuncs.com      domain  \n",
       "0  minerjson.oss-cn-beijing.aliyuncs.com      domain  \n",
       "0  minerjson.oss-cn-beijing.aliyuncs.com      domain  \n",
       "0  minerjson.oss-cn-beijing.aliyuncs.com      domain  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# gather meta data about target (itw url)\n",
    "itw_enrichment = vt_client.lookup_iocs(cryptominer_itw_df, 'target', 'target_type', all_props=True)\n",
    "itw_enrichment = itw_enrichment[ ['id', 'url'] ]\n",
    "itw_enrichment['host'] = itw_enrichment.apply(lambda x:get_host(x.url)[1], axis=1)\n",
    "itw_enrichment['host.type'] = itw_enrichment.apply(lambda x: ('ip_address', 'domain')[len(re.findall(r\"[A-z]{1,}\", x.host)) > 0], axis=1)\n",
    "itw_enrichment.drop_duplicates(inplace=True)\n",
    "\n",
    "display(itw_enrichment.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging and Summarizing Findings\n",
    "The final step is to merge, format, and summarize the findings by using methods native to pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source</th>\n",
       "      <th>source.name</th>\n",
       "      <th>source.type</th>\n",
       "      <th>target</th>\n",
       "      <th>target.name</th>\n",
       "      <th>target_type</th>\n",
       "      <th>target.host</th>\n",
       "      <th>relationship_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd</td>\n",
       "      <td>config.json</td>\n",
       "      <td>file</td>\n",
       "      <td>81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d</td>\n",
       "      <td>http://27.1.1.34:8080/docs/config.json</td>\n",
       "      <td>url</td>\n",
       "      <td>27.1.1.34</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd</td>\n",
       "      <td>zbetcheckin_tracker_config.json</td>\n",
       "      <td>file</td>\n",
       "      <td>81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d</td>\n",
       "      <td>http://27.1.1.34:8080/docs/config.json</td>\n",
       "      <td>url</td>\n",
       "      <td>27.1.1.34</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>server2.0.0.json</td>\n",
       "      <td>file</td>\n",
       "      <td>ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c</td>\n",
       "      <td>https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546</td>\n",
       "      <td>url</td>\n",
       "      <td>minerjson.oss-cn-beijing.aliyuncs.com</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>C:\\Users\\&lt;USER&gt;\\Downloads\\server.json</td>\n",
       "      <td>file</td>\n",
       "      <td>ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c</td>\n",
       "      <td>https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546</td>\n",
       "      <td>url</td>\n",
       "      <td>minerjson.oss-cn-beijing.aliyuncs.com</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad</td>\n",
       "      <td>C:\\Users\\user\\Desktop\\server.json</td>\n",
       "      <td>file</td>\n",
       "      <td>ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c</td>\n",
       "      <td>https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546</td>\n",
       "      <td>url</td>\n",
       "      <td>minerjson.oss-cn-beijing.aliyuncs.com</td>\n",
       "      <td>itw_urls</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                             source  \\\n",
       "0  d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd   \n",
       "1  d8d6ff48c3d2df3c8289e3c519e15b9a2110d08975eaddef995d95e4c0d970cd   \n",
       "2  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad   \n",
       "3  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad   \n",
       "4  a80a424f11f3a55cd427011485b95f1f93b7f8492a1cf5dbe5ada1df4ca0f5ad   \n",
       "\n",
       "                             source.name source.type  \\\n",
       "0                            config.json        file   \n",
       "1        zbetcheckin_tracker_config.json        file   \n",
       "2                       server2.0.0.json        file   \n",
       "3  C:\\Users\\<USER>\\Downloads\\server.json        file   \n",
       "4      C:\\Users\\user\\Desktop\\server.json        file   \n",
       "\n",
       "                                                             target  \\\n",
       "0  81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d   \n",
       "1  81734d74cdbc7a2b58442e8bae0f5b0352717346107e30d37e8e041248fe503d   \n",
       "2  ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c   \n",
       "3  ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c   \n",
       "4  ac441d52afe44184afbf85baab9722c94859dbf834b95359e3c16a26fe70538c   \n",
       "\n",
       "                                                                           target.name  \\\n",
       "0                                               http://27.1.1.34:8080/docs/config.json   \n",
       "1                                               http://27.1.1.34:8080/docs/config.json   \n",
       "2  https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546   \n",
       "3  https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546   \n",
       "4  https://minerjson.oss-cn-beijing.aliyuncs.com/server2.0.0.json?t=638058298707060546   \n",
       "\n",
       "  target_type                            target.host relationship_type  \n",
       "0         url                              27.1.1.34          itw_urls  \n",
       "1         url                              27.1.1.34          itw_urls  \n",
       "2         url  minerjson.oss-cn-beijing.aliyuncs.com          itw_urls  \n",
       "3         url  minerjson.oss-cn-beijing.aliyuncs.com          itw_urls  \n",
       "4         url  minerjson.oss-cn-beijing.aliyuncs.com          itw_urls  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>target.host</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45.90.220.62</th>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cdn.discordapp.com</th>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>raw.githubusercontent.com</th>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>minerjson.oss-cn-beijing.aliyuncs.com</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27.1.1.34</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>k2ygoods.top</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>main.cloudfronts.net</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50.63.143.208</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hk.kuai-go.com</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>safe.kuai-go.com</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       source\n",
       "target.host                                  \n",
       "45.90.220.62                               45\n",
       "cdn.discordapp.com                         45\n",
       "raw.githubusercontent.com                  45\n",
       "minerjson.oss-cn-beijing.aliyuncs.com      15\n",
       "27.1.1.34                                   2\n",
       "k2ygoods.top                                2\n",
       "main.cloudfronts.net                        2\n",
       "50.63.143.208                               1\n",
       "hk.kuai-go.com                              1\n",
       "safe.kuai-go.com                            1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# merge, reorder, and rename dataframe\n",
    "cryptominer_itw_df = cryptominer_itw_df.merge(itw_enrichment, how='left', left_on='target', right_on='id')\n",
    "cryptominer_itw_df = cryptominer_itw_df[ ['source', 'source.name', 'source_type', 'target', 'url', 'target_type', 'host', 'relationship_type', ] ]\n",
    "cryptominer_itw_df.rename(columns={\"source_type\": \"source.type\", \"url\": \"target.name\", 'host':'target.host'}, inplace=True)\n",
    "\n",
    "display(cryptominer_itw_df.head())\n",
    "\n",
    "display(cryptominer_itw_df.groupby('target.host').count()[['source']].sort_values('source', ascending=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sys\n",
    "sys.getrecursionlimit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
